package cn.com.nes.site.module.dao.monitor;

import cn.com.nes.common.util.*;
import cn.com.nes.mybatis.agent.ope.entity.Xzqh;
import cn.com.nes.site.entity.DataReq;
import cn.com.nes.site.entity.PageData;
import cn.com.nes.site.module.dao.BaseDaoImpl;

import cn.hutool.core.util.StrUtil;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class EquipmentInfoDaoImpl extends BaseDaoImpl {

    public String getWxIdByWxId(String sbid) {
        Connection conn = null;
        try {
            conn = DBUtils.getJdbcForOpe();
            StringBuffer sb = new StringBuffer();
            sb.append("select wx_id from is_equipment_info where wx_id=? and sfsc = 0 ");
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{sbid});
            if (list.size() > 0) {
                return (String) list.get(0).get("wx_id");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
        }
        return "";
    }

    public String getWxSbByWxsbId(String sbid) {
        Connection conn = null;
        try {
            conn = DBUtils.getJdbcForOpe();
            StringBuffer sb = new StringBuffer();
            sb.append("select wx_id from is_wxsb where wx_id=? and sfsc = 0 ");
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{sbid});
            if (list.size() > 0) {
                return (String) list.get(0).get("wx_id");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
        }
        return "";
    }

    public String getICCID(DataReq req) {
        Connection conn = null;
        try {
            String sbid = (String) req.getReqData().get("sbId");
            conn = DBUtils.getJdbcForOpe();
            StringBuffer sb = new StringBuffer();
            sb.append("select t2.wx_kh from is_equipment_info t1 left join is_wxsb t2 on t1.wx_id=t2.wx_id where t1.sb_id=? ");
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{sbid});
            if (list.size() > 0) {
                return (String) list.get(0).get("wx_kh");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
        }
        return "";
    }


    public String getDeviceWorkOrder(DataReq req) {
        Connection conn = null;
        try {
            String sbid = (String) req.getReqData().get("sbId");
            conn = DBUtils.getJdbcForOpe();
            StringBuffer sb = new StringBuffer();
            sb.append("SELECT a.relation_id FROM is_work_order_equipment_info a LEFT JOIN is_equipment_info b ON b.sb_id=a.sb_id WHERE b.sb_id=? and b.sfsc=0  ");
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{sbid});
            if (list.size() > 0) {
                return (String) list.get(0).get("relation_id");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
        }
        return "";
    }

    public String getSbxlhBySbId(DataReq req) {
        Connection conn = null;
        try {
            String sbid = (String) req.getReqData().get("sbId");
            conn = DBUtils.getJdbcForOpe();
            StringBuffer sb = new StringBuffer();
            sb.append("SELECT sbxlh FROM is_equipment_info WHERE sb_id=? AND sfsc=0 ");
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{sbid});
            if (list.size() > 0) {
                return (String) list.get(0).get("sbxlh");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
        }
        return "";
    }


    public PageData getWxList(DataReq req) {
        PageData res = new PageData();
        Connection conn = null;
        try {
            String manufactureId = req.getUinfo().getNes_companyId();
            String wxId = (String) req.getReqData().get("wxId");

            conn = DBUtils.getJdbcForOpe();
            StringBuffer sb = new StringBuffer();
            sb.append("select wx_id from is_wxsb where sfsc = 0 and is_test = 0 ");
            if (StringUtils.isNotBlank(wxId)) {
                sb.append(" and wx_id like '%").append(wxId).append("%'");
            }
            sb.append(" and (manufacture_id ='").append(manufactureId).append("' or agent_id='").append(manufactureId).append("')");
            sb.append(" and not EXISTS (select 1 from is_equipment_info where is_wxsb.wx_id=is_equipment_info.wx_id and sfsc = 0 and manufacture_id='").append(manufactureId).append("')");

            sb = DBUtils.sql_check(sb);
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            return PageData.error("查询失败");
        } finally {
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
        }
        return res;
    }

//    public int updateTimeStampByWxId(String wxid) {
//        Connection conn = null;
//        try
//        {
//            conn = DBUtils.getJdbcForOpe();
//            StringBuffer sb = new StringBuffer();
//            sb.append("update is_wxsb set xgsj= now(),version = UNIX_TIMESTAMP(now(3)) * 1000 where wx_id=? ");
//            int result = this.update(conn, sb.toString(), new Object[]{wxid});
//            conn.commit();
//            return result;
//        }catch (Exception e) {
//            e.printStackTrace();
//        } finally {
//            if (conn != null) {
//                try {
//                    conn.close();
//                } catch (SQLException e) {
//                    // TODO Auto-generated catch block
//                    e.printStackTrace();
//                }
//            }
//        }
//        return 0;
//    }

    public PageData setUntyingSecret(DataReq req) {

        PageData res = new PageData();
        Connection conn = null;
        try {
            String manufactureId = req.getUinfo().getNes_companyId();
            String secret = (String) req.getReqData().get("secret");
            conn = DBUtils.getJdbcForOpe();
            StringBuffer sb = new StringBuffer();
            sb.append("update is_manufacture_info set untying_secret=? where manufacture_id=?  ");
            int result = this.update(conn, sb.toString(), new Object[]{secret, manufactureId});
            conn.commit();
            if (result > 0) {
                res.setRetCode(0);
                res.setRetMessage("OK");
            } else {
                return PageData.error("更新失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
            return PageData.error("更新失败");
        } finally {
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
        }
        return res;
    }

    public void updateZbqByDdid(DataReq req) throws Exception {
        Connection conn = null;
        try {
            String manufactureId = req.getUinfo().getNes_companyId();
            String zbq = (String) req.getReqData().get("zbrq");
            String ddId = (String) req.getReqData().get("dd_id");
            conn = DBUtils.getJdbcForOpe();
            StringBuffer sb = new StringBuffer();
            sb.append("update is_equipment_info set zbq=?,xgsj =NOW() where dd_id=? and sfsc = 0 and manufacture_id=?  ");
            int result = this.update(conn, sb.toString(), new Object[]{zbq, ddId, manufactureId});
            conn.commit();
            if (result > 0) {
            } else {
                throw new Exception("更新失败");
                // return PageData.error("更新失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
        }
    }

    public PageData editInstallInfo(DataReq req) {
        PageData res = new PageData();
        Connection conn = null;
        try {

            conn = DBUtils.getJdbcForOpe();

            String manufactureId = req.getUinfo().getNes_companyId();
            String zbq = (String) req.getReqData().get("zbrq");
            String jd = (String) req.getReqData().get("jd");
            String wd = (String) req.getReqData().get("wd");
            String name = (String) req.getReqData().get("name");
            String phone = (String) req.getReqData().get("phone");
            String xzqhdm = (String) req.getReqData().get("xzqhdm");
            String address = (String) req.getReqData().get("address");
            String dd_id = (String) req.getReqData().get("dd_id");

            StringBuffer sb = new StringBuffer();

            sb.append("update is_equipment_info set zbq=?,xgsj =NOW() where dd_id=? and sfsc = 0 and manufacture_id=?  ");
            int result = this.update(conn, sb.toString(), new Object[]{zbq, dd_id, manufactureId});
            if (result > 0) {
            } else {
                throw new Exception("更新失败");
            }

            sb.delete(0, sb.length());
            sb.append("update is_install_info set ");
            if (jd != null) {
                sb.append("jd='").append(jd).append("',");
            }

            if (wd != null) {
                sb.append("wd='").append(wd).append("',");
            }

            if (name != null) {
                sb.append("xmlxr='").append(name).append("',");
            }

            if (phone != null) {
                sb.append("xmlxrdh='").append(phone).append("',");
            }

            if (xzqhdm != null) {
                sb.append("xzqh='").append(xzqhdm).append("',");
            }

            if (address != null) {
                sb.append("xmdz='").append(address).append("',");
            }

            if (sb != null && sb.length() > 0 && sb.charAt(sb.length() - 1) == ',') {
                sb.deleteCharAt(sb.length() - 1);
            }

            sb.append(" where dd_id =? ");
            result = this.update(conn, sb.toString(), new Object[]{dd_id});
            conn.commit();
            if (result > 0) {
                res.setRetCode(0);
                res.setRetMessage("OK");
            } else {
                throw new Exception("更新失败");
            }
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return PageData.error("更新失败");
        } finally {
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
        }
        return res;
    }

    public PageData getSbCountByXzqh(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询设备信息失败");
        Connection conn = null;
        try {

            conn = DBUtils.getJdbcForOpe();
            String xzqh = (String) req.getReqData().get("xzqh");
            String xzqhjb = XzqhUtil.getXzqhjb(conn, xzqh);
            String startTime = (String) req.getReqData().get("startTime");
            String endTime = (String) req.getReqData().get("endTime");
            String areaCodeLikeStr = "";
            if (StringUtils.isNotEmpty(xzqh)) {
                areaCodeLikeStr = XzqhUtils.getAreaCodeLikeStr(xzqh, xzqhjb);
            }
            String manufactureId = (String) req.getReqData().get("manufactureId");

            String companyType = req.getUinfo().getCompanyType();
            StringBuffer sb = new StringBuffer();
            sb.append(" select count(1) sl,sum(g.work_order_id is not null) as qjqnsl,0 as sysbsl, 1 as zzansl,sum(if(g.is_online = 1, 1,0)) as countNotOnline,sum(if(g.is_online = 0, 0,0)) as countOnlineNum,ifnull(h.xzqhmc, '未知地区') xzqhmc, h.xzqhdm, h.zxjd jd, h.zxwd wd, h.xzqhjb from ( ");
            sb.append(" SELECT ");
            if (xzqhjb.equals("1")) {
                sb.append(" concat(substring(b.xzqh, 1, 4), '00000000') xzqhdm ");
            } else if (xzqhjb.equals("2")) {
                sb.append(" concat(substring(b.xzqh,1,6),'000000') xzqhdm ");
            } else if (xzqhjb.equals("3")) {
                sb.append("  concat(substring(b.xzqh,1,9),'000') xzqhdm ");
            } else if (xzqhjb.equals("4")) {
                sb.append("  b.xzqh xzqhdm ");
            } else if (xzqhjb.equals("5")) {
                sb.append(" b.xzqh xzqhdm ");
            } else {
                /*sb.append(" substring(b.xzqh,1,2) xzqhdm ");*/
                sb.append(" concat(substring(b.xzqh,1,6),'000000') xzqhdm ");
            }
            sb.append(" , ifnull(c.is_online,1) as  is_online,a.work_order_id FROM is_equipment_info a ");
            sb.append(" INNER JOIN is_install_info b ON a.dd_id = b.dd_id ");
            sb.append(" LEFT JOIN is_norm_data c ON c.sb_id = a.sb_id");
            /*
            sb.append(" LEFT JOIN t_sblx ts ON a.sblx_id = ts.sblx_id ");
            sb.append(" LEFT JOIN is_norm_data_b c ON c.sb_id = a.sb_id");
            sb.append(" AND c.dtu_id = a.wx_id");
            sb.append("  LEFT JOIN is_norm_data_b d ON d.sb_id = a.sb_id AND d.fault_code IS NOT NULL ");
            sb.append(" AND d.dtu_id = a.wx_id ");
            sb.append(" LEFT JOIN is_norm_data_b e ON e.sb_id = a.sb_id and e.is_warn = 1 ");
            sb.append(" AND e.dtu_id = a.wx_id AND e.fault_code IS NULL");
            sb.append(" LEFT join t_xzqh f on f.xzqhdm = b.xzqh");
            sb.append(" left join is_user_info iui on iui.user_id = a.create_oper");
             */

            sb.append("  WHERE  a.sfsc = 0 and (a.sbyt !='workorder' or a.sbyt is null) and a.sfzj != 2 "); // AND a.sjly != 'ctce'
            if (StringUtils.isNotEmpty(startTime)) {
                sb.append(" and a.cjsj >= '").append(startTime).append("') ");
            }
            if (StringUtils.isNotEmpty(endTime)) {
                sb.append(" and a.cjsj <= '").append(endTime).append("') ");
            }
            if (StringUtils.isNotEmpty(xzqh) && !xzqh.equals("86")) {
                sb.append(" and b.xzqh LIKE CONCAT(").append(areaCodeLikeStr).append(", '%')  ");
            }
            if (StringUtils.isNotEmpty(manufactureId)) {
                sb.append(" and a.manufacture_id='").append(manufactureId).append("' ");
            }

            sb.append(" and b.sfsc = 0 ");

            if ("01".equals(companyType)) {
                //厂商
                sb.append(" and a.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
            } else if ("02".equals(companyType)) {
                //代理商
                sb.append(" AND a.manufacture_id in(select facturer_id from nes_agent.t_agent_facturer where agent_id= '").append(req.getUinfo().getCompanyId()).append("' ) ");
                sb.append(" and a.agent_id like '%").append(req.getUinfo().getCompanyId()).append("%' ");
            } else {
                //管理员
            }
            sb.append("  ) g left join t_xzqh h on g.xzqhdm = h.xzqhdm  group by xzqhmc ");
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
        }
        return res;
    }


    public PageData getSbCountByBrand(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询设备信息失败");
        Connection conn = null;
        try {
            conn = DBUtils.getJdbcForOpe();
            String xzqh = (String) req.getReqData().get("xzqh");
            String xzqhjb = XzqhUtil.getXzqhjb(conn, xzqh);
            String startTime = (String) req.getReqData().get("startTime");
            String endTime = (String) req.getReqData().get("endTime");
            String areaCodeLikeStr = XzqhUtils.getAreaCodeLikeStr(xzqh, xzqhjb);
            String manufactureId = (String) req.getReqData().get("manufactureId");

            String companyType = req.getUinfo().getCompanyType();
            StringBuffer sb = new StringBuffer();
            sb.append(" select count(1) sl, sum(if(g.is_online = 1, 1,0)) as countNotOnline,sum(if(g.is_online = 0, 0,0)) as countOnlineNum,0 as kjgz, 0 as gjgz,h.manufacture_id manufactureId,h.name manufactureName from ( ");
            sb.append(" SELECT a.manufacture_id,IFNULL(c.is_online , 1) as is_online ");
            sb.append("  FROM is_equipment_info a ");
            sb.append(" INNER JOIN is_install_info b ON a.dd_id = b.dd_id ");
            sb.append(" LEFT JOIN is_norm_data c ON c.sb_id = a.sb_id");
            /*
            sb.append(" LEFT JOIN t_sblx ts ON a.sblx_id = ts.sblx_id ");
            sb.append(" LEFT JOIN is_norm_data_b c ON c.sb_id = a.sb_id");
            sb.append(" AND c.dtu_id = a.wx_id");
            sb.append("  LEFT JOIN is_norm_data_b d ON d.sb_id = a.sb_id AND d.fault_code IS NOT NULL ");
            sb.append(" AND d.dtu_id = a.wx_id ");
            sb.append(" LEFT JOIN is_norm_data_b e ON e.sb_id = a.sb_id and e.is_warn = 1 ");
            sb.append(" AND e.dtu_id = a.wx_id AND e.fault_code IS NULL");
            sb.append(" LEFT join t_xzqh f on f.xzqhdm = b.xzqh");
            sb.append(" left join is_user_info iui on iui.user_id = a.create_oper");*/

            sb.append("  WHERE  a.sfsc = 0 and (a.sbyt !='workorder' or a.sbyt is null)  and a.sfzj != 2 "); //AND a.sjly != 'ctce'
            if (StringUtils.isNotEmpty(startTime)) {
                sb.append(" and a.cjsj >= '").append(startTime).append("') ");
            }
            if (StringUtils.isNotEmpty(endTime)) {
                sb.append(" and a.cjsj <= '").append(endTime).append("') ");
            }
            if (StringUtils.isNotEmpty(xzqh) && !xzqh.equals("86")) {
                sb.append(" and b.xzqh LIKE CONCAT(").append(areaCodeLikeStr).append(", '%')  ");
            }
            if (StringUtils.isNotEmpty(manufactureId)) {
                sb.append(" and a.manufacture_id='").append(manufactureId).append("' ");
            }
            sb.append(" and b.sfsc = 0 ");
            if ("01".equals(companyType)) {
                //厂商
                sb.append(" and a.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
            } else if ("02".equals(companyType)) {
                //代理商
                sb.append(" AND a.manufacture_id in(select facturer_id from nes_agent.t_agent_facturer where agent_id= '").append(req.getUinfo().getCompanyId()).append("' ) ");
                sb.append(" and a.agent_id like '%").append(req.getUinfo().getCompanyId()).append("%' ");
            } else {
                //管理员
            }
            sb.append("  ) g left join is_manufacture_info h on g.manufacture_id = h.manufacture_id  group by name ");
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
        }
        return res;
    }


    public PageData getSbCountBySjly(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询设备信息失败");
        Connection conn = null;
        try {

            conn = DBUtils.getJdbcForOpe();
            String xzqh = (String) req.getReqData().get("xzqh");
            String xzqhjb = XzqhUtil.getXzqhjb(conn, xzqh);
            String startTime = (String) req.getReqData().get("startTime");
            String endTime = (String) req.getReqData().get("endTime");
            String areaCodeLikeStr = XzqhUtils.getAreaCodeLikeStr(xzqh, xzqhjb);
            String manufactureId = (String) req.getReqData().get("manufactureId");

            String companyType = req.getUinfo().getCompanyType();
            StringBuffer sb = new StringBuffer();
            sb.append(" select count(1) sl,g.sjly, if(g.sjly='ctce','清洁取暖',if(g.sjly='ope','运营',if(g.sjly='mtpf','厂商','热链云'))) sjlyName from ( ");
            sb.append(" SELECT ");
//            if (xzqhjb.equals("1")) {
//                sb.append(" concat(substring(b.xzqh, 1, 4), '00000000') xzqhdm ");
//            } else if (xzqhjb.equals("2")) {
//                sb.append(" concat(substring(b.xzqh,1,6),'000000') xzqhdm ");
//            } else if (xzqhjb.equals("3")) {
//                sb.append("  concat(substring(b.xzqh,1,9),'000') xzqhdm ");
//            } else if (xzqhjb.equals("4")) {
//                sb.append("  b.xzqh xzqhdm ");
//            } else if (xzqhjb.equals("5")) {
//                sb.append(" b.xzqh xzqhdm ");
//            }else {
//                sb.append(" substring(b.xzqh,1,2) xzqhdm ");
//            }
            sb.append(" a.sjly ");
            sb.append("  FROM is_equipment_info a ");
            sb.append(" INNER JOIN is_install_info b ON a.dd_id = b.dd_id ");

            /*
            sb.append(" LEFT JOIN t_sblx ts ON a.sblx_id = ts.sblx_id ");
            sb.append(" LEFT JOIN is_norm_data_b c ON c.sb_id = a.sb_id");
            sb.append(" AND c.dtu_id = a.wx_id");
            sb.append("  LEFT JOIN is_norm_data_b d ON d.sb_id = a.sb_id AND d.fault_code IS NOT NULL ");
            sb.append(" AND d.dtu_id = a.wx_id ");
            sb.append(" LEFT JOIN is_norm_data_b e ON e.sb_id = a.sb_id and e.is_warn = 1 ");
            sb.append(" AND e.dtu_id = a.wx_id AND e.fault_code IS NULL");
            sb.append(" LEFT join t_xzqh f on f.xzqhdm = b.xzqh");
            sb.append(" left join is_user_info iui on iui.user_id = a.create_oper");*/

            sb.append("  WHERE  a.sfsc = 0 and (a.sbyt !='workorder' or a.sbyt is null) and a.sfzj != 2 ");
            if (!startTime.isEmpty()) {
                sb.append(" and a.cjsj >= '").append(startTime).append("') ");
            }
            if (!endTime.isEmpty()) {
                sb.append(" and a.cjsj <= '").append(endTime).append("') ");
            }
            if (!xzqh.isEmpty() && !xzqh.equals("86")) {
                sb.append(" and b.xzqh LIKE CONCAT(").append(areaCodeLikeStr).append(", '%')  ");
            }
            if (!manufactureId.isEmpty()) {
                sb.append(" and a.manufacture_id='").append(manufactureId).append("' ");
            }

            sb.append(" and b.sfsc = 0 ");

            if ("01".equals(companyType)) {
                //厂商
                sb.append(" and a.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
            } else if ("02".equals(companyType)) {
                //代理商
                sb.append(" AND a.manufacture_id in(select facturer_id from nes_agent.t_agent_facturer where agent_id= '").append(req.getUinfo().getCompanyId()).append("' ) ");
                sb.append(" and a.agent_id like '%").append(req.getUinfo().getCompanyId()).append("%' ");
            } else {
                //管理员
            }
//            sb.append("  ) g left join t_xzqh h on g.xzqhdm = h.xzqhdm  group by xzqhmc ");
            sb.append("  ) g group by sjlyName ");
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
        }
        return res;
    }

    public PageData getSbCountByEquipmentType(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询设备信息失败");
        Connection conn = null;
        try {
            conn = DBUtils.getJdbcForOpe();
            String manufactureId = (String) req.getReqData().get("manufactureId");
            StringBuffer sb = new StringBuffer();
            sb.append(" select sb.sblx_id, lx.sblxmc, count(sb.sb_id) as sl, CONCAT(ROUND(COUNT(*) / (select count(1) from is_equipment_info sb\n" +
                    "    inner join is_install_info iii on sb.dd_id = iii.dd_id\n" +
                    "         inner join t_sblx lx on sb.sblx_id = lx.sblx_id and sb.manufacture_id = lx.manufacture_id\n");
            if (!manufactureId.isEmpty()) {
                sb.append(" where sb.manufacture_id='").append(manufactureId).append("' ");
            }
            sb.append("  and sb.sfsc = 0) * 100, 2), '%') AS percentage from is_equipment_info sb\n" +
                    "    inner join is_install_info iii on sb.dd_id = iii.dd_id\n" +
                    "         inner join t_sblx lx on sb.sblx_id = lx.sblx_id and sb.manufacture_id = lx.manufacture_id");
            sb.append(" where sb.sfsc = 0 ");
            if (!manufactureId.isEmpty()) {
                sb.append(" and sb.manufacture_id='").append(manufactureId).append("' ");
            }
            sb.append("  group by sb.sblx_id ");
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
        }
        return res;
    }

    public PageData getEquipment(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询设备信息失败");
        Connection conn = null;
        try {
            conn = DBUtils.getJdbcForOpe();
            StringBuffer sb = new StringBuffer();
            StringBuffer sbfrom = new StringBuffer();
            sb.append("select iei.id id,iei.manufacture_id,imi.name manufactureName, iei.sjly,if(iei.sjly='ctce','清洁取暖',if(iei.sjly='ope','运营',if(iei.sjly='mtpf','厂商','热链云'))) sjlyName,iei.sb_id                                                                                  as sbId,         ");
            sb.append("              if(iei.zbq, datediff(iei.zbq, now()), 0)                                                   as sbxlh,             ");
            sb.append("              iei.wx_id                                                                         as wxId,              ");
            sb.append("              iei.dd_id                                                                                  as ddId,              ");
            sb.append("              iii.xmlxr                                                                        as name,              ");
            sb.append("              iii.xmlxrdh                                                                       as phone,             ");
            sb.append("              iii.xmdz                                                                                   as address,           ");
            sb.append("              idvi.title                                                                                 as sblx,              ");
            sb.append("              iei.sblx_id                                                                                as sblxId,            ");
            sb.append("              ts.sbxh,                                                                                                         ");
            sb.append("              if(ind.is_online = 0 and ind2.gmt_create>DATE_ADD(now(),INTERVAL -60 MINUTE), '在线', '离线')                                                          as isOnline,      ");
            sb.append("              if(ind.is_online = 0 and ind2.gmt_create>DATE_ADD(now(),INTERVAL -60 MINUTE) , if(ind.fault_code != '', '2', if(ind.is_warn = 1, '3', '1')), '4') as state,       ");//1 正常，2故障，3预警，4离线
            sb.append("              if(ind.is_online = 0, if(ind.a350 = 0, '关机', '开机'), '')                                       onOff,         ");
            sb.append("              iui.xm                                                                                     as latestEditor,      ");
            sb.append("              date_format(iei.xgsj, '%Y-%m-%d %T')               as latestUpdateTime,  ");
            sb.append("              iei.alias,                                                                                                       ");
            sb.append("              iii.xzqh,                                                                                                        ");
            sb.append("              tx.xzqhmc,                                                                                                       ");
            //sb.append("              if(iuf.id is null, 0, 1)                                                                   as isFollow,          ");
            sb.append("              ind2.gmt_create                                                                            as cjsj,              ");
            sb.append("              iii.jd,                                                                                                          ");
            sb.append("              iii.wd,                                                                                                          ");
            sb.append("              ifnull(iii.azt,'')                                                                            azt,               ");
            sb.append("              ifnull(iiwoi.images,'')                                                                       images,            ");
            sb.append("              REPLACE(tx.xzqhqc, '->', '')                                                                  xzqhqc,            ");
            sb.append("              date_format(iei.zbq, '%Y-%m-%d')                                                              zbrq,              ");
            sb.append("              date_format(iii.cjsj, '%Y-%m-%d')                                                             installDate,       ");
            sb.append("              ts.sblxmc,                                                                                                       ");
            sb.append("              iei.sbxlh                                                                                     sbxlh,      ");
            //sb.append("              wxsb.wx_kh,                                                                                                      ");
            //sb.append("              date_format(wxsb.cjsj, '%Y-%m-%d')                                                            dtuRegDate,        ");
            sb.append("              b.`name`                                                                                      protocolName,      ");
            sb.append("              iei.protocol_id                                                                            as protocolId,        ");
            sb.append("              if(DATE_SUB(now(), INTERVAL 30 MINUTE) > ind2.gmt_create, 0, 1)                               devStatus,         ");
            sb.append("              if(ind2.is_online = 0, ind2.fault_codes, '')                                               as faultCodes,        ");
            sb.append("              iei.dealer_name                                                                            as dealerName,        ");
            sb.append("               iei.dealer_phone                                                                           as dealerPhone,       ");
            sb.append("             iui.user_id                                                                                as createUserId       ");
            sb.append("  from nes_ope_prod.is_equipment_info iei ");
            sb.append("           inner join nes_ope_prod.is_install_info iii on (iii.dd_id = iei.dd_id and iii.sfsc = 0)                                           ");
            sb.append("           left join nes_ope_prod.is_device_protocol_info b on b.protocol_id = iei.protocol_id                                               ");
            sb.append("           left join nes_ope_prod.t_sblx ts on (ts.sblx_id = iei.sblx_id and ts.sfsc = 0)                                                    ");
            sb.append("           left join nes_ope_prod.is_dict_value_info idvi on idvi.type_id = 'd_type_2' and idvi.dic_no = ts.sblx                             ");
            sb.append("           left join nes_ope_prod.is_norm_data_b ind on ind.dtu_id = iei.wx_id and ind.sb_id = iei.sb_id                                     ");
            sb.append("       left join nes_ope_prod.is_norm_data ind2 on ind2.dtu_id = iei.wx_id and ind2.sb_id = iei.sb_id                                        ");
            sb.append("       left join nes_ope_prod.is_user_info iui on iui.user_id = iei.create_oper                                                              ");
            sb.append("       left join nes_ope_prod.t_xzqh tx on tx.xzqhdm = iii.xzqh                                                                              ");
            sb.append("       left join nes_ope_prod.is_install_work_order_info iiwoi on (iei.dd_id = iiwoi.dd_id  and iei.sb_id = iiwoi.sb_id and iiwoi.sfsc = 0)       LEFT JOIN nes_ope_prod.is_manufacture_info imi ON iei.manufacture_id = imi.manufacture_id                        ");
            //sb.append("       left join nes_ope_prod.is_wxsb wxsb on iei.wx_id = wxsb.wx_id                                                                         ");
            //sb.append("       left join nes_ope_prod.is_user_follow iuf on iuf.userId = '0' and iuf.sbId = iei.sb_id                                          ");
            sb.append("       where iei.sfsc = 0                                                                                                       ");
            sb.append("         and iei.sfzj != 2                                                                                                      ");
            sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, req.getUinfo().getAreacode(), "iii.xzqh"));
            String companyType = req.getUinfo().getCompanyType();
            String manufactureId = (String) req.getReqData().get("manufactureId");
            if (StrUtil.isNotEmpty((String) req.getReqData().get("sbId"))) {
                //解决故障预警->故障列表详情数据为空  是否影响其他查询接口，暂时未发现
            } else if (!Globals.DEMO_COMPANYID.equals(req.getUinfo().getCompanyId())) {//不是演示账号
                if ("01".equals(companyType)) {//厂商
                    sb.append(" and iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
                } else if ("02".equals(companyType)) {//代理商
                    sb.append(" AND iei.manufacture_id in(select facturer_id from nes_agent.t_agent_facturer where agent_id= '").append(req.getUinfo().getCompanyId()).append("' ) ");
                    sb.append(" and iei.agent_id= '").append(req.getUinfo().getCompanyId()).append("' ");
                } else {//管理员
//                    sb.append(" and (ind.is_online = 0 and ind2.gmt_create>DATE_ADD(now(),INTERVAL -60 MINUTE)) ");
//
//                    if (StringUtils.isEmpty(manufactureId)){
//                        sb.append("and iei.manufacture_id='MA3NU7QX1' ");
//                    }
                }
            } else {//演示账号默认德尚
                sb.append(" and (ind.is_online = 0 and ind2.gmt_create>DATE_ADD(now(),INTERVAL -60 MINUTE)) and iei.manufacture_id='MA3NU7QX1' ");
            }

//            if (StrUtil.isNotEmpty((String) req.getReqData().get("sbId"))) {
//                //解决故障预警->故障列表详情数据为空  是否影响其他查询接口，暂时未发现
//            } else if (!Globals.DEMO_COMPANYID.equals(req.getUinfo().getCompanyId())) {//不是演示账号
//                if ("01".equals(companyType)) {//厂商
//                    sb.append(" and iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
//                } else if ("02".equals(companyType)) {//代理商
//                    sb.append(" and iei.agent_id= '").append(req.getUinfo().getCompanyId()).append("' ");
//                }
//            }

            String ismap = (String) req.getReqData().get("ismap");
            if ("1".equals(ismap)) {
                sb.append(" and iii.jd is not null and  iii.wd is not null ");
            }

            if (!StringUtils.isEmpty(manufactureId)) {
                sb.append("and iei.manufacture_id = '").append(manufactureId).append("'");
            }

            String is_master = (String) req.getReqData().get("is_master");
            if (!StringUtils.isEmpty(is_master)) {
                if ("1".equals(is_master)) {
                    sb.append(" and iei.is_master= 1 ");
                } else {
                    sb.append(" and (iei.is_master is null or iei.is_master <> 1 ) ");
                }
            }

            String state = (String) req.getReqData().get("state");

            ////1 正常（绿），2故障（红），3预警（橙），4离线 （灰）
            if (!StringUtils.isEmpty(state)) {
                switch (state) {
                    case "4"://离线
                        sb.append(" and not (ind.is_online = 0 and ind2.gmt_create>DATE_ADD(now(),INTERVAL -60 MINUTE)) ");
                        break;
                    case "3"://预警
                        sb.append(" and (ind.is_online = 0 and ind2.gmt_create>DATE_ADD(now(),INTERVAL -60 MINUTE) and ind.is_warn = 1) ");
                        break;
                    case "2"://故障
                        sb.append(" and (ind.is_online = 0 and ind2.gmt_create>DATE_ADD(now(),INTERVAL -60 MINUTE) and ind.fault_code != '') ");
                        break;
                    case "1"://在线
                        sb.append(" and (ind.is_online = 0 and ind2.gmt_create>DATE_ADD(now(),INTERVAL -60 MINUTE))  ");
                        break;

                    default:
                        break;
                }
            }

            if (!StringUtils.isEmpty((String) req.getReqData().get("xzqhdm")) && !"86".equals((String) req.getReqData().get("xzqhdm"))) {
                sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, (String) req.getReqData().get("xzqhdm"), "iii.xzqh"));
            }

            if (!StringUtils.isEmpty((String) req.getReqData().get("cjsj_min"))) {
                sb.append(" and iii.cjsj >='").append(req.getReqData().get("cjsj_min")).append("' ");
            }

            if (!StringUtils.isEmpty((String) req.getReqData().get("cjsj_max"))) {
                sb.append(" and iii.cjsj <='").append(req.getReqData().get("cjsj_max")).append("' ");
            }

            if ("1".equals((String) req.getReqData().get("sfgb"))) {
                sb.append(" and iei.zbq<now() ");
            }

            String name = (String) req.getReqData().get("name");
            if (!StringUtils.isEmpty(name)) {
                sb.append(" and (iii.xmlxr like concat('%','").append(name).append("', '%') or iii.xmlxrdh like concat('%','").append(name).append("', '%') ) ");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("phone"))) {
                sb.append(" and iii.xmlxrdh like concat('%','").append(req.getReqData().get("phone")).append("', '%') ");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("address"))) {
                sb.append(" and iii.xmdz like concat('%','").append(req.getReqData().get("address")).append("', '%') ");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("sbId"))) {
                sb.append(" and iei.sb_id ='").append(req.getReqData().get("sbId")).append("' ");
            }
            if (!StringUtils.isEmpty((String) req.getReqData().get("wxId"))) {
                sb.append(" and iei.wx_id like concat('%','").append(req.getReqData().get("wxId")).append("', '%') ");
            }
            sb.append(" and (iei.sjly='ctce' or iei.sjly='ope' or iei.sjly='mtpf' or iei.sjly='agent' )");
            if (!StringUtils.isEmpty((String) req.getReqData().get("sjly"))) {
                sb.append(" and iei.sjly ='").append(req.getReqData().get("sjly")).append("' ");
            }
//			sb.append("and iei.v1 = (select max(iei2.v1) from nes_ope_prod.is_equipment_info iei2 where iei.sb_id = iei2.sb_id)");
            sb = DBUtils.sql_check(sb);
            sbfrom.append(sb.toString());
            int idx = sbfrom.indexOf(" from ");
            sbfrom.delete(0, idx);
            sb.append(" order by iei.xgsj desc ");
            Page page = new Page(conn, sbfrom.toString(), req.getPageid(), req.getPagesize(), res, new Object[]{});
            sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
            List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
            if (StrUtil.isNotEmpty((String) req.getReqData().get("sbId"))) {
                Map<String, Object> stringObjectMap = list.get(0);
                String azts = String.valueOf(stringObjectMap.get("azt"));
                String images = String.valueOf(stringObjectMap.get("images"));
                ArrayList aztList = new ArrayList();
                ArrayList imageList = new ArrayList();

                if ("ctce".equals(String.valueOf(stringObjectMap.get("sjly")))) {
                    if (StrUtil.isNotEmpty(azts)) {
                        String[] azt = azts.split(";");
                        for (String i : azt) {
                            aztList.add(OssUtil.getImgSignUrlWithWaterMark(i, 3, Globals.bucketName_ctce, "清洁取暖工程管理"));
                        }
                        stringObjectMap.put("azt", aztList);
                    } else {
                        stringObjectMap.put("azt", aztList);
                    }
                    if (StrUtil.isNotEmpty(images)) {
                        String[] image = images.split(",");
                        for (String i : image) {
                            imageList.add(OssUtil.getImgSignUrlWithWaterMark(i, 4, Globals.bucketName_mtpf, "清洁取暖工程管理"));
                        }
                        stringObjectMap.put("images", imageList);
                    } else {
                        stringObjectMap.put("images", imageList);
                    }
                } else {
                    if (StrUtil.isNotEmpty(azts)) {
                        String[] azt = azts.split(";");
                        for (String i : azt) {
                            aztList.add(OssUtil.getImgSignURL(i, 0, Globals.bucketName_ctce));
                        }
                        stringObjectMap.put("azt", aztList);
                    } else {
                        stringObjectMap.put("azt", aztList);
                    }
                    if (StrUtil.isNotEmpty(images)) {
                        String[] image = images.split(",");
                        for (String i : image) {
                            imageList.add(OssUtil.getImgSignURL(i, 0, Globals.bucketName_mtpf));
                        }
                        stringObjectMap.put("images", imageList);
                    } else {
                        stringObjectMap.put("images", imageList);
                    }
                }
            }
            //转换经纬度
            for (int i = 0; i < list.size(); i++) {
                List<Object> lnglat = new ArrayList<Object>();
                lnglat.add(list.get(i).get("jd"));
                lnglat.add(list.get(i).get("wd"));
                list.get(i).put("lnglat", lnglat);
                list.get(i).put("style", Integer.parseInt((String) list.get(i).get("state")));
            }


            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
        }
        return res;
    }

    public static void main(String[] args) {
        String manufactureId = "00000157";
        StringBuffer sb = new StringBuffer();
        sb.append(" select sb.sblx_id, lx.sblxmc, count(sb.sb_id) as sl, CONCAT(ROUND(COUNT(*) / (select count(1) from is_equipment_info sb\n" +
                "    inner join is_install_info iii on sb.dd_id = iii.dd_id\n" +
                "         inner join t_sblx lx on sb.sblx_id = lx.sblx_id and sb.manufacture_id = lx.manufacture_id\n");
        if (!manufactureId.isEmpty()) {
            sb.append(" where sb.manufacture_id='").append(manufactureId).append("' ");
        }
        sb.append("  and sb.sfsc = 0) * 100, 2), '%') AS percentage from is_equipment_info sb\n" +
                "    inner join is_install_info iii on sb.dd_id = iii.dd_id\n" +
                "         inner join t_sblx lx on sb.sblx_id = lx.sblx_id and sb.manufacture_id = lx.manufacture_id");
        sb.append(" where sb.sfsc = 0 ");
        if (!manufactureId.isEmpty()) {
            sb.append(" and sb.manufacture_id='").append(manufactureId).append("' ");
        }
        sb.append("  group by sb.sblx_id ");
        System.out.println(sb);
    }
}
